<?php

require_once("core/SqlProfile.php");
require_once("core/SqlExpr.php");
require_once("core/TableFieldMap.php");


class Table{
	
	var $_records;
	var $_cols;
	var $_colnames;
	var $_pkeys;
	var $profile;
	var $_tableclass;
	var $hasError;
	var $_coltypes;
	
	
	function Table($tablename){
		$this->name = DB_PREFIX.$tablename;
		$this->tablename = DB_PREFIX.$tablename;
	}
	
	function init(){
		
		$this->_is_described=false;
	}
	
	function _describe(){
		static $table_props;
		//var_dump($_is_described[$this->name]);
		if(!$table_props[$this->name]){
			if(!isset($this->name))
				$this->name = DB_PREFIX.$this->tablename;
			global $__sqlprofile;
			$this->profile=&$__sqlprofile;
		
			$this->hasError=false;
			
			$r=$this->query("DESCRIBE {$this->name}");
			
			while($row=mysql_fetch_assoc($r)){
				$this->_cols[$row["Field"]]=$row;
				//var_dump($row);
				//if($row["Key"]=="PRI") $this->_pkeys[]=$row["Field"];
				if($row["Key"]=="PRI") $this->_pkeys[$row["Field"]]=$row;
				$this->_coltypes[$row["Field"]]=ereg_replace("\(.+\)","",$row["Type"]);
			}
			
			$table_props[$this->name]["_cols"]=$this->_cols;
			$table_props[$this->name]["_coltypes"]=$this->_coltypes;
			$table_props[$this->name]["_pkeys"]=$this->_pkeys;
			//$this->_is_described=true;
		}else{
			$this->_cols=$table_props[$this->name]["_cols"];
			$this->_coltypes=$table_props[$this->name]["_coltypes"];
			$this->_pkeys=$table_props[$this->name]["_pkeys"];
		}
		
		return $this->_cols;
		//}
		//var_dump($this->_coltypes);
	}
	
	function _hasPrimaryKeys($data){
		$this->_describe();
		$keys = array_keys($data);
		$pkcount = count($this->_pkeys);
		foreach($keys as $key){
			if(in_array($key,array_keys($this->_pkeys))) {
				if(--$pkcount==0) return true;
			}
		}
		return false;
	}
	
	function query($sql){
		global $__sqlprofile;
		$__sqlprofile->capture($sql,$this->_tableclass);
		$r=mysql_query($sql) or $this->_error();
		return $r;
	}
	
	function _error(){
		$this->hasError=true;
		$this->profile->error($this->_tableclass);
	}
	
	function listfield(){
		$rows=array();
		$result = mysql_query("SHOW COLUMNS FROM {$this->name}");
		while($row=mysql_fetch_assoc($result))$rows[]=$row["Field"];
		mysql_free_result($result);
		
		return $rows;
	}
	
	function _markFieldList(){
		if($this->fieldlist==NULL) {
			$this->fieldlist=$this->listfield();
			$this->isHadArrange=in_array("arrange",$this->fieldlist);
		}
		return $this->fieldlist;
	}
	
	
	function getone($pks,$colname){
		if(!is_array($pks)){
			$pkcol = addslashes(current(array_keys($this->_pkeys)));
			return $this->selectone($colname,"WHERE {$pkcol} = '$pks'");
		}
	}
	
	function getrow($id,$cols="*"){
		$rows=$this->select($cols,"WHERE id = '$id'");
		return $rows[0];
	}
	
	function getrows($ids,$cols="*"){
		$sql="SELECT $cols FROM {$this->name} WHERE id in (".implode(",",$ids).") ";
		$this->lastsql=$sql;
		$result = mysql_query($sql);
		$rows=array();
		while($row=mysql_fetch_assoc($result)){
			$rows[]=$row;
		}		
		mysql_free_result($result);
		return $rows;
	}
	
	
	function _selectString($sqlcmds=NULL,$colSql=NULL,$join=NULL,$params=NULL){
	
		if($this->hasCol("unit_id")){
			if($join==NULL){
				$join=leftjoin("unit","create_at,update_at,trash_at,is_trash","unit_id=id");
			}else{
				$join=array_merge(leftjoin("unit","create_at,update_at,trash_at,is_trash","unit_id=id"),$join);
			}
		}
	
		if($sqlcmds==NULL)$sqlcmds="";
		$orderCmds = array("WHERE","ORDER","LIMIT");
		
		
		//this column name of table
		if($colSql==NULL) $colSql="*";
		
		if(is_string($colSql)){
			$cols = explode(",",$colSql);
			$colSql="";
			foreach($cols as $col) {
				
				$col=trim($col);
				if($this->hasCol(strtolower($col)) || $col=="*"){
					$colSql.=$this->name.".".$col.", ";
				}else{
					$colSql.=$col.", ";
				}
			}
			
			$colSql=substr($colSql,0,-2);
		}
		
		if(is_array($colSql)){
			$cols = $colSql;
			foreach($cols as $i=>$col) {
				if(is_a($col,"SqlExpr")){
					$cols[$i]=$col->value;
				}else{
					$cols[$i]="{$this->name}.$col";
				}
			}
			$colSql=implode(",",$cols);
		}
		
		if(is_a($colSql,"SqlExpr")){
			$colSql = $colSql->value;
		}
		
				
		
		if(isset($join["join"])){
			if ($join!=NULL && $join["col"]!=NULL){
				$colSql.=", ";
				$cols=explode(",",$join["col"]);
				foreach($cols as $i=>$col) $colSql.=DB_PREFIX.$join[table].".".$col.", ";
				$colSql=substr($colSql,0,-2);
			}
		}else{
			if(is_array($join)){
				foreach($join as $j){
					if ($j!=NULL && $j["col"]!=NULL){
						$colSql.=", ";
						$cols=explode(",",$j["col"]);
						foreach($cols as $i=>$col) $colSql.=DB_PREFIX.$j[table].".".$col.", ";
						$colSql=substr($colSql,0,-2);
					}
				}
			}
		}
		
		
		if(!$params["distinct"]){
			$sql="SELECT $colSql FROM {$this->name} ";
		}else{
			$sql="SELECT DISTINCT $colSql FROM {$this->name} ";
		}
		//JOIN
		if ($join!=NULL){
			if(isset($join["join"])){
				$sql.=$join["join"]." ";
				$sql.=DB_PREFIX.$join["table"]." ON";
				$ons = explode("=",$join["on"]);
				
				if(!strpos($ons[0],".")) $ons[0]=$this->name.".".$ons[0];
				if(!strpos($ons[1],".")) $ons[1]=DB_PREFIX.$join["table"].".".$ons[1];
				
				$join["on"]=implode("=",$ons);
				//$join["on"]=str_replace(":table",$this->name,$join["on"]);
				$sql.="({$join[on]})";
			}else{
				if(is_array($join)){
					foreach($join as $j){
						$sql.=$j["join"]." ";
						$sql.=DB_PREFIX.$j["table"]." ON";
						$ons = explode("=",$j["on"]);
						
						if(!strpos($ons[0],".")) $ons[0]=$this->name.".".$ons[0];
						if(!strpos($ons[1],".")) $ons[1]=DB_PREFIX.$j["table"].".".$ons[1];
						
						$j["on"]=implode("=",$ons);
						//$join["on"]=str_replace(":table",$this->name,$join["on"]);
						$sql.="({$j[on]}) ";
					}
				}
			}
		}
		
		if(is_array($sqlcmds)){
			foreach($orderCmds as $cmd){
				if(isset($sqlcmds[$cmd])){
					$q=$sqlcmds[$cmd];
					if($cmd=="ORDER")$q=" BY ".$q;
					$sql.=" ".$cmd." ".$q." ";
				}
			}
		}
		
		if(is_string($sqlcmds)){
			$sql.=" ".$sqlcmds;
		}
		
		return array("query"=>$sql,"map"=>$map_cols);
	}
	
	function selectDistinct($colSql=NULL,$sqlcmds=NULL,$join=NULL){
		$params["distinct"]=true;
		$this->select($colSql,$sqlcmds,$join,$params);
	}
	
	
	function select($colSql=NULL,$sqlcmds=NULL,$join=NULL,$params=NULL){
		
		$this->_describe();
		$r=$this->_selectString($sqlcmds,$colSql,$join,$params);
		$sql=$r["query"];
		$map_cols=$r["map"];
		
		$rows=array();
		$this->lastsql=$sql;
	
		//var_dump($this->_cols);
		
		$result = $this->query($sql);
		if(!$result) return array();
		while($row=mysql_fetch_assoc($result)) {
			$rrow=array();
			foreach($row as $f=>$v){
				
				switch ($this->_coltypes[$f]){
					case "mediumblob":
						$rrow[$f]=$v;
						break;
					case "int":
						$rrow[$f]=intval($v);
						break;
					case "decimal":
						$rrow[$f]=floatval($v);
						break;
					case "float":
						$rrow[$f]=floatval($v);
						break;
					
					default:
						$rrow[$f]=stripslashes(stripslashes($v));
				}	
			}
			$rows[]=$rrow;
		}
		mysql_free_result($result);
		
		return $rows;
	}
	
	function selectrow($colSql=NULL,$sqlcmds=NULL,$join=NULL){
		
		if(is_string($sqlcmds)) {
			$sql.=" LIMIT 0,1";
		}
		
		$rows=$this->select($colSql,$sqlcmds,$join);
		
		return $rows[0];
		
	}
	
	function selectOne($colname,$sqlcmds=null,$join=null){
		$row = $this->selectrow($colname,$sqlcmds,$join=null);
		return $row[$colname];
	}
	
	function selectOneList($colname,$sqlcmds=null,$join=null){
		return $this->selectList($colname,$sqlcmds,$join);
	}
	
	function selectList($colname,$sqlcmds=null,$join=null){
		$rows = $this->select($colname,$sqlcmds,$join);
		$arrs=array();
		foreach($rows as $row) $arrs[]=$row[$colname];
		return $arrs;
	}
	
	function _combineSET($datas){
		$SETs=array(); 
		foreach($datas as $col=>$value) {
			if(is_a($value,"SqlExpr")){
				$SETs[] = " {$col}={$value->value}";
			}else{
				$SETs[] = " {$col}='".addslashes($value)."' ";
			}
		}
		return implode(",",$SETs);
	}
	
	function insert($data){
		
		$this->_describe();
		
		
		$data=$this->_filterKeysAndPk($data);
		
		if($this->hasCol("unit_id")) {
			$unit_table_name = ereg_replace("^".DB_PREFIX,"",$this->name);
			$unit_time = date("YmdHis");
			$sql="INSERT INTO ".DB_PREFIX."unit SET tablename='{$unit_table_name}', create_at='{$unit_time}' , update_at='{$unit_time}'";
			$this->query($sql);
			
			$dat["unit_id"]=mysql_insert_id();	
		}
		
		$sql="INSERT INTO {$this->name} SET ".$this->_combineSET($data);
		$this->query($sql);
		return mysql_insert_id();
	}
	
	function replace($data){
		$this->_describe();
		
		
		$data=$this->_filterKeys($data);
		
		
		$sql="REPLACE INTO {$this->name} SET ".$this->_combineSET($data);
		$this->query($sql);
		
	}
	
	function update($data,$where=null){
		
		$this->_describe();
		
		if(!$this->_hasPrimaryKeys($data) && $where==NULL) trigger_error("Missing Primary Key");
	
		$where_sql=0;
		if($where==null){
			foreach((array_read($this->_pkeys,"[*][Field]")) as $colname){
				$whereks[]="{$colname}='{$data[$colname]}'";
			}
			$where_sql = implode(" AND ",$whereks);
		}else{
			$where=preg_replace("/^WHERE/i ","",$where);
			$where_sql = $where;
		}
		
		$data=$this->_filterKeysAndPk($data);
		
		if(in_array("update_at",array_keys($this->_cols))) $data["update_at"]=expr("NOW()");
		
		
		$sql="UPDATE {$this->name} SET ";
		$sql.=$this->_combineSET($data);
		$sql.=" WHERE {$where_sql}";
		
		if($this->hasCol("unit_id")) {
			$unit_ids = $this->selectList("unit_id","WHERE {$where_sql}");
			if(count($unit_ids)>0){
				$sql="UPDATE ".DB_PREFIX."unit SET update_at='".date("YmdHis")."' WHERE id IN (".quote($unit_ids).")";
				$this->query($sql);
			}
		}
		
		$this->query($sql);
		
		return mysql_affected_rows();
	}
	
	
	/*function updatec($data,$condition){
		if(empty($data["modified_at"])) $data["modified_at"]=date("YmdHis");
		$data=$this->_filterKeysAndPk($data);
		$sql="UPDATE {$this->name} SET ";
		$sql.=$this->_combineSET($data);
		$sql.= " ".$condition;
		$this->query($sql);
		return mysql_affected_rows();
	}*/
	
	function save($data){
		$this->_describe();
		$pk_fields = array_read($this->_pkeys,"[*][Field]");
		
		foreach($pk_fields as $pk){
			if(trim($data[$pk])=="") unset($data[$pk]);
		}
		
		if(count(array_diff($pk_fields,array_intersect(array_keys($data),$pk_fields)))	>0){
			return $this->insert($data);
		}else{
			$this->update($data);
		}
			
		return $data[$pk_fields[0]];
	}
	
	
	
	
	function delete($ids){	
		$this->_describe();
		if(is_string($ids)){
			if(preg_match("/^WHERE +.*/i",$ids)){ 
				return $this->deleteWhere($ids);
			}
		}
		
		if(!is_array($ids)){
			$ids=array($ids);
		}
		
		$ids = array_map("addslashes",$ids);
		if(count($this->_pkeys)==1) {
			$pk = current(array_keys($this->_pkeys));
			return $this->deleteWhere("WHERE {$pk} IN (".quote($ids).")");
		}
		
	}
	
	function deleteWhere($where){
		$this->_describe();
		$where_sql=0;
		$where=preg_replace("/^WHERE/i","",$where);
		$where_sql = $where;
		
	
		if($this->hasCol("unit_id")){
			$unit_ids = $this->selectList("unit_id","WHERE {$where_sql}");
			if(count($unit_ids)>0){
				$sql="UPDATE ".DB_PREFIX."unit SET is_trash=1, trash_at='".date("YmdHis")."' WHERE id IN (".quote($unit_ids).")";
				$this->query($sql);
			}
		}else{
			$this->query("DELETE FROM {$this->name} WHERE {$where_sql}");
			return mysql_affected_rows();
		}
	}
	
	
	function hasCol($colname){
		$this->_describe();
		return in_array($colname,array_keys($this->_cols));
	}
	
	
	
	function getPKNames(){
		$this->_describe();
		
		return array_keys($this->_pkeys);
	}
	
	function getColNames(){
		$this->_describe();
		return array_keys($this->_cols);
	}
	
	function getColNamesWithoutPk(){
		return array_diff($this->getColNames(),$this->getPKNames());
	}
	
	function _filterKeysAndPk($arr){
		$this->_describe();
		$data=array();
		foreach($this->_cols as $colname=>$col){
			if(!in_array($colname,array_keys($this->_pkeys)) && in_array($colname,array_keys($arr))){
				$data[$colname]=$arr[$colname];
			}
		}
		
		return $data;
	}
	
	function _filterKeys($arr){
		$this->_describe();
		$data=array();
		foreach($this->_cols as $colname=>$col){
			if(in_array($colname,array_keys($arr))){
				$data[$colname]=$arr[$colname];
			}
		}
		
		return $data;
	}
	
	
	function count($condition=NULL,$join=NULL){
		$r=$this->selectrow(new SqlExpr("count(*) AS count"),$condition,$join);
		return $r["count"];
	}
	
	function getError(){
		SqlProfile::GetErrors($this->_tableclass);
	}
	
	function expr($value){
		return new SqlExpr($value);
	}
	
	function get($id,$cols="*"){
		return $this->getrow($id,$cols);
	}
	
	
	
}


$__TablePools;
function table($tablename){
	global $__TablePools;
	if(!isset($__TablePools[$tablename])){
		$__TablePools[$tablename]=new Table($tablename);
	}
	return $__TablePools[$tablename];
}



//until

function leftJoin($tablename,$colSQL=NULL,$onSQL){
	return array("join"=>"LEFT JOIN","table"=>$tablename,"on"=>$onSQL,"col"=>$colSQL);
}
function rightJoin($tablename,$onSQL,$colSQL=NULL){
	return array("join"=>"RIGHT JOIN","table"=>$tablename,"on"=>$onSQL,"col"=>$colSQL);
}

function innerJoin($tablename,$colSQL=NULL,$onSQL){
	return array("join"=>"INNER JOIN","table"=>$tablename,"on"=>$onSQL,"col"=>$colSQL);
}

function outerjoin($tablename,$onSQL,$colSQL=NULL){
	return array("join"=>"OUTER JOIN","table"=>$tablename,"on"=>$onSQL,"col"=>$colSQL);
}




function ids($data){
	$ids=array();
	foreach($data as $row){
		if(isset($row[id]) && $row[id]!="") $ids[]=$row[id];
	}
	return $ids;
}

function idAsKey($data){
	$rows=array();
	foreach($data as $row){
		if(isset($row[id]))$rows[$row[id]]=$row;
	}
	return $rows;
}




?>
